<?php

namespace app\service;

use app\exception\HttpResponseException;
use app\model\EntityModel;
use app\model\ReviewFeedbackEntityModel;
use app\model\ReviewFeedbackModel;
use app\model\StatusModel;
use app\tool\SingletonTrait;
use support\ErrorCode;

/**
 * 实体需求统计
 */
class EntityStatisticsService
{
    use SingletonTrait;

    /**
     * @var 实体关闭状态id
     */
    private $statusClosedId;
    /**
     * @var 实体完成状态id
     */
    private $statusFinalId;

    // 统计
    const RESPONSE_FORMAT_TYPE_COUNT = 'count';
    // 分组统计
    const RESPONSE_FORMAT_TYPE_GROUP_BY = 'group_by';
    // 列表
    const RESPONSE_FORMAT_TYPE_LIST = 'list';

    // 响应返回格式字段
    const RESPONSE_FORMAT_TYPE_KEY_MAP = [
        self::RESPONSE_FORMAT_TYPE_COUNT => 'count',
        self::RESPONSE_FORMAT_TYPE_GROUP_BY => 'group_by_list',
        self::RESPONSE_FORMAT_TYPE_LIST => 'list'
    ];

    public function __construct()
    {
        $this->setStatusId();
    }

    /**
     * 获取新增需求数
     * @param array $projectIds 项目id集合
     * @param array $time 时间范围。[开始时间, 结束时间]
     * @param string $isStandardAppend 是否为标准新增需求。yes:标准新增 | no:非标准新增
     * @param string $moduleCode 关联模块。asset:资产 | shot:镜头
     * @param int $stageId 阶段id
     * @param string $fields 字段查询
     * @param string $responseFormatType 返回格式
     * @param string $groupBy 分组字段
     * @param array $page 分页 [页数, 每页数量]
     * @return array {"count": 统计次数, "list": [], "group_by_list": []}
     * @throws HttpResponseException
     */
    public function getEntityAppendDemand(
        array  $projectIds,
        array  $time,
        string $isStandardAppend = '',
        string $moduleCode = '',
        int    $stageId = 0,
        string $fields = '',
        string $responseFormatType = '',
        string $groupBy = '',
        array  $page = []
    )
    {
        $condition = [
            // 项目id
            'entity.project_id' => $projectIds,
            // 有新增标识
            'entity.is_append' => EntityModel::IS_APPEND_YES,
            // 获取已发布的需求
            'review_feedback_entity.published' => ReviewFeedbackEntityModel::PUBLISHED_YES,
        ];

        // 是否为标准新增
        $this->getReviewFeedbackStandardAppendCondition($condition, $isStandardAppend);

        // 提出阶段
        $this->getReviewFeedbackStageIdCondition($condition, $stageId);

        // 关联模块
        $this->getEntityModuleIdCondition($condition, $moduleCode);

        // 查询数据
        $fields = $fields ?: ['entity.id'];
        $entityQuery = EntityModel::field($fields)
            ->join('review_feedback_entity', 'review_feedback_entity.entity_id = entity.id', 'LEFT')
            ->join('review_feedback', 'review_feedback.id = review_feedback_entity.review_feedback_id')
            ->where($condition);

        // 排除废除的实体，但包含已完成的废除实体
        $entityQuery = $this->entityStatusQuery($entityQuery, $moduleCode);
        $entityQuery = $entityQuery->whereBetween('entity.created', $time);

        // 分页
        if (!empty($page)) {
            $entityQuery = $entityQuery->page(...$page);
        }

        return $this->buildResByResponseFormatType($entityQuery, $responseFormatType, $groupBy);
    }

    /**
     * 获取基本量
     * 定义：无新增标识的实际产量
     * @param array $projectIds 项目id集合
     * @param array $time 时间范围。[开始时间, 结束时间]
     * @param string $moduleCode 关联模块。asset:资产 | shot:镜头
     * @param string $fields 字段查询
     * @param string $responseFormatType 返回格式
     * @param string $groupBy 分组字段
     * @param array $page 分页 [页数, 每页数量]
     * @return array {"count": 统计次数, "list": [], "group_by_list": []}
     * @throws HttpResponseException
     */
    public function getBasicDemand(
        array  $projectIds,
        array  $time,
        string $moduleCode = '',
        string $fields = '',
        string $responseFormatType = '',
        string $groupBy = '',
        array  $page = []
    )
    {
        $condition = [
            // 项目id
            'entity.project_id' => $projectIds,
            // 获取无新增需求标识
            'entity.is_append' => EntityModel::IS_APPEND_NO,
            // 获取已发布的需求
            'review_feedback_entity.published' => ReviewFeedbackEntityModel::PUBLISHED_YES,
        ];
        // 关联模块code
        $this->getEntityModuleIdCondition($condition, $moduleCode);

        $fields = $fields ?: ['entity.id'];
        // 查询数据
        $entityQuery = EntityModel::field($fields)
            ->join('review_feedback_entity', 'review_feedback_entity.entity_id = entity.id', 'LEFT')
            ->where($condition);
        // 排除废除的实体，但包含已完成的废除实体
        $entityQuery = $this->entityStatusQuery($entityQuery, $moduleCode);
        $entityQuery = $entityQuery->whereBetween('entity.created', $time);

        // 分页
        if (!empty($page)) {
            $entityQuery = $entityQuery->page(...$page);
        }

        return $this->buildResByResponseFormatType($entityQuery, $responseFormatType, $groupBy);
    }

    /**
     * 获取明细列表
     * @param array $projectIds 项目id
     * @param array $time 时间范围。[开始时间, 结束时间]
     * @param string $episodeCode 集数
     * @param string $isStandardAppend 是否为标准新增需求。yes:标准新增 | no:非标准新增
     * @param string $moduleCode 关联模块code
     * @param int $stageId 阶段id
     * @param string $fields 字段
     * @param string $responseFormatType 返回格式
     * @param string $groupBy 分组字段
     * @return array|array[] {"count": 统计次数, "list": [], "group_by_list": []}
     * @throws HttpResponseException
     */
    public function getDemandDetail(
        array  $projectIds,
        array  $time,
        string $episodeCode = '',
        string $isStandardAppend = '',
        string $moduleCode = '',
        int    $stageId = 0,
        string $fields = '',
        string $responseFormatType = '',
        string $groupBy = ''
    )
    {
        $condition = [
            // 项目id
            'project_id' => $projectIds,
            // 获取已启用的需求单
            'review_feedback_entity.enable' => ReviewFeedbackEntityModel::ENABLE_YES
        ];

        // 是否为标准新增
        $this->getReviewFeedbackStandardAppendCondition($condition, $isStandardAppend);
        // 组装关联模块条件
        $this->getReviewFeedbackModuleCodeCondition($condition, $moduleCode);
        // 组装提出阶段条件
        $this->getReviewFeedbackStageIdCondition($condition, $stageId);
        // 组装集数条件
        $this->getReviewFeedbackEpisodeCodeCondition($condition, $episodeCode);
        // 查询数据
        $fields = $fields ?: ['review_feedback.id'];
        $res = ReviewFeedbackModel::field($fields)
            ->join('review_feedback_entity', 'review_feedback_entity.review_feedback_id = review_feedback.id', 'LEFT')
            ->where($condition)
            ->whereBetween('review_feedback.created', $time)
            ->select();
        if (empty($res)) {
            return [];
        }

        // 需求单次数需要根据需求单id聚合
        $list = $this->buildGroupByReviewFeedbackId($res->toArray());

        // 统计需求单次数
        if ($responseFormatType == self::RESPONSE_FORMAT_TYPE_COUNT) {
            return ['count' => count($list)];
        }

        // 根据分组字段进行分组统计
        if ($responseFormatType == self::RESPONSE_FORMAT_TYPE_GROUP_BY && !empty($groupBy)) {
            return ['group_by_list' => $this->countGroupByColumn($list, $groupBy)];
        }

        // 默认返回列表
        return ['list' => $list];
    }

    /**
     * 根据需求单id聚合
     * @param $data
     * @return array
     */
    private function buildGroupByReviewFeedbackId($data)
    {
        $res = [];
        // 根据review_feedback_id聚合
        foreach ($data as $item) {
            $res[$item['id']] = $item;
        }

        return array_values($res);
    }

    /**
     * 根据需求单id获取资产分类数
     * @param array $reviewFeedbackIds 需求单id集合
     * @return array {资产类型: [资产id集合]}
     */
    public function getAssetCategoryCount(array $reviewFeedbackIds)
    {
        $res = EntityModel::field(['count(entity.id) AS count', 'get_json_string(`entity`.`json`, "$.design_category") AS category'])
            ->join('review_feedback_entity', 'review_feedback_entity.entity_id = entity.id')
            ->where(['review_feedback_entity.review_feedback_id' => $reviewFeedbackIds])
            ->group('category')
            ->select();
        if (empty($res)) {
            return [];
        }
        return $res->toArray();
    }

    /**
     * 获取预估量
     * @param array $projectIds 项目id集合
     * @param array $time 时间范围。[开始时间, 结束时间]
     * @param string $moduleCode 关联模块。asset:资产 | shot:镜头
     * @param string $fields 字段查询
     * @param string $responseFormatType 返回格式
     * @param string $groupBy 分组字段
     * @param array $page 分页 [页数, 每页数量]
     * @return array {"count": 统计次数, "list": [], "group_by_list": []}
     * @throws HttpResponseException
     */
    public function getEntityEstimate(
        array  $projectIds,
        array  $time,
        string $moduleCode = '',
        string $fields = '',
        string $responseFormatType = '',
        string $groupBy = '',
        array  $page = []
    )
    {
        $condition = [
            // 项目id
            'entity.project_id' => $projectIds,
            // 获取已发布的需求
            'review_feedback_entity.published' => ReviewFeedbackEntityModel::PUBLISHED_YES,
        ];
        // 关联模块code
        $this->getEntityModuleIdCondition($condition, $moduleCode);

        $fields = $fields ?: ['entity.project_id'];
        // 查询数据
        $entityQuery = EntityModel::field($fields)
            ->join('review_feedback_entity', 'review_feedback_entity.entity_id = entity.id', 'LEFT')
            ->where($condition);

        $entityQuery = $entityQuery->whereBetween('entity.created', $time);

        // 分页
        if (!empty($page)) {
            $entityQuery = $entityQuery->page(...$page);
        }

        return $this->buildResByResponseFormatType($entityQuery, $responseFormatType, $groupBy);
    }

    /**
     * 根据需求单id获取实体统计
     * @param array $reviewFeedbackIds 需求单id集合
     * @param string $moduleCode 关联模块code (asset 资产 shot 镜头)
     * @param string $responseFormatType 返回格式
     * @return array|array[]
     * @throws HttpResponseException
     */
    public function getEntityStatisticsByReviewFeedbackIds(array $reviewFeedbackIds, string $moduleCode = '', string $responseFormatType = '')
    {
        $condition = [
            'review_feedback_entity.review_feedback_id' => $reviewFeedbackIds
        ];
        // 组装关联模块code
        $this->getReviewFeedbackEntityModuleIdCondition($condition, $moduleCode);

        // 查询数据
        $dbQuery = EntityModel::field(['entity.id'])
            ->join('review_feedback_entity', 'review_feedback_entity.entity_id = entity.id')
            ->where($condition);

        return $this->buildResByResponseFormatType($dbQuery, $responseFormatType);
    }

    /**
     * 组装实体module_id条件
     * @param array $condition 条件数组
     * @param string $moduleCode 关联模块code
     */
    public function getEntityModuleIdCondition(array &$condition, string $moduleCode)
    {
        // 关联模块
        if (!empty($moduleCode)) {
            $convertModuleCode = $this->convertModuleCode($moduleCode);
            $moduleId = ModuleService::getInstance()->byCode($convertModuleCode, 'id');
            $condition['entity.module_id'] = $moduleId;
        }
    }

    /**
     * 组装需求单standard_append条件
     * @param array $condition 条件数组
     * @param string $isStandardAppend 是否为标准新增需求。yes:标准新增 | no:非标准新增
     */
    public function getReviewFeedbackStandardAppendCondition(array &$condition, string $isStandardAppend)
    {
        // 关联模块
        if (!empty($isStandardAppend)) {
            $condition['review_feedback.standard_append'] = trim($isStandardAppend);
        }
    }

    /**
     * 组装需求单stage_id条件
     * @param array $condition 条件数组
     * @param int $stageId 提出阶段id
     */
    public function getReviewFeedbackStageIdCondition(array &$condition, int $stageId)
    {
        // 关联模块
        if (!empty($stageId)) {
            $condition['review_feedback.stage_id'] = $stageId;
        }
    }

    /**
     * 组装需求单standard_append条件
     * @param array $condition 条件数组
     * @param string $episodeCode 集数code
     */
    public function getReviewFeedbackEpisodeCodeCondition(array &$condition, string $episodeCode)
    {
        // 关联模块
        if (!empty($episodeCode)) {
            $condition['review_feedback.episode_codes'] = trim($episodeCode);
        }
    }

    /**
     * 组装需求单module_code条件
     * @param array $condition 条件数组
     * @param string $moduleCode 关联模块 (asset 资产 shot 镜头)
     */
    public function getReviewFeedbackModuleCodeCondition(array &$condition, string $moduleCode)
    {
        // 关联模块
        if (!empty($moduleCode)) {
            $condition['review_feedback.module_code'] = trim($moduleCode);
        }
    }

    /**
     * 组装审核反馈实体表实体module_id条件
     * @param array $condition 条件数组
     * @param string $moduleCode 关联模块 (asset 资产 shot 镜头)
     */
    public function getReviewFeedbackEntityModuleIdCondition(array &$condition, string $moduleCode)
    {
        // 关联模块
        if (!empty($moduleCode)) {
            // 需要将code转换，审核反馈实体表的资产关联模块code为design，镜头code为storyboard
            $convertModuleCode = $moduleCode == ReviewFeedbackModel::MODULE_CODE_ASSET ? ReviewFeedbackEntityModel::MODULE_CODE_DESIGN : ReviewFeedbackEntityModel::MODULE_CODE_STORYBOARD;
            $moduleId = ModuleService::getInstance()->byCode($convertModuleCode, 'id');
            $condition['review_feedback_entity.entity_module_id'] = $moduleId;
        }
    }

    /**
     * 组装实体状态查询语句
     * @param $queryDb
     * @param string $moduleCode 关联模块code。
     * @return mixed
     */
    public function entityStatusQuery($queryDb, string $moduleCode)
    {
        if (!empty($moduleCode)) {
            // 如果关联code不为空，根据关联code查询
            $convertModuleCode = $this->convertModuleCode($moduleCode);
            $sql = $this->getEntityStatusSql($convertModuleCode);
        } else {
            // 如果关联code为空，概念、分镜都查询
            $sql = "({$this->getEntityStatusSql(ReviewFeedbackEntityModel::MODULE_CODE_DESIGN)}) OR ({$this->getEntityStatusSql(ReviewFeedbackEntityModel::MODULE_CODE_STORYBOARD)})";
        }
        return $queryDb->whereRaw($sql);
    }

    /**
     * 组装实体状态查询sql
     * @param $moduleCode
     * @return string
     */
    private function getEntityStatusSql($moduleCode)
    {
        // 排除已废除状态，但包含已完成的废除实体
        return "get_json_string(`entity`.`json`, '$.{$moduleCode}_status') = {$this->statusClosedId} AND get_json_string(`entity`.`json`, '$.{$moduleCode}_deprecate_data.{$moduleCode}_status') = {$this->statusFinalId} OR get_json_string(`entity`.`json`, '$.{$moduleCode}_status') <> {$this->statusClosedId}";
    }

    /**
     * 资产装概念，镜头转分镜
     * @param $moduleCode
     * @return string
     */
    private function convertModuleCode($moduleCode)
    {
        return $moduleCode == ReviewFeedbackModel::MODULE_CODE_ASSET ? ReviewFeedbackEntityModel::MODULE_CODE_DESIGN : ReviewFeedbackEntityModel::MODULE_CODE_STORYBOARD;
    }

    /**
     * 设置状态id
     */
    private function setStatusId()
    {
        $this->statusClosedId = StatusService::getInstance()->byCode(StatusModel::STATUS_CLOSED)['id'];
        $this->statusFinalId = StatusService::getInstance()->byCode(StatusModel::STATUS_FINAL)['id'];
    }

    /**
     * 根据字段分组统计次数
     * @param array $data
     * @param string $column 需要分组的字段
     * @return array [{分组维度值: 总数}]
     */
    private function countGroupByColumn(array $data, string $column)
    {
        $res = [];
        foreach ($data as $item) {
            // 统计数量
            $res[$item[$column]] = !empty($res[$item[$column]]) ? $res[$item[$column]] + 1 : 1;
        }
        return $res;
    }

    /**
     * 根据响应格式类型组装数据
     * @param $dbQuery
     * @param string $type 响应格式类型
     * @param string $groupBy 分组字段
     * @return array|array[]
     * @throws HttpResponseException
     */
    public function buildResByResponseFormatType($dbQuery, string $type, string $groupBy = '')
    {
        try {
            switch ($type) {
                // 统计返回的格式 {"count": 次数}
                case self::RESPONSE_FORMAT_TYPE_COUNT:
                    $res = [
                        self::RESPONSE_FORMAT_TYPE_KEY_MAP[self::RESPONSE_FORMAT_TYPE_COUNT] => $dbQuery->count()
                    ];
                    break;

                // 分组返回的格式 {"group_by_list": [{分组字段: 次数}]}
                case self::RESPONSE_FORMAT_TYPE_GROUP_BY:
                    // 如果未传分组字段，返回空数组
                    if (empty($groupBy)) {
                        return [];
                    }
                    $queryRes = $dbQuery->select()->toArray();
                    $res = [
                        self::RESPONSE_FORMAT_TYPE_KEY_MAP[self::RESPONSE_FORMAT_TYPE_GROUP_BY] => $this->countGroupByColumn($queryRes, $groupBy)
                    ];
                    break;

                // 列表返回的格式 ，默认为列表格式{"list": [{字段名: 值}]}
                default:
                    $res = [
                        self::RESPONSE_FORMAT_TYPE_KEY_MAP[self::RESPONSE_FORMAT_TYPE_LIST] => $dbQuery->select()->toArray()
                    ];
                    break;
            }

            return $res;
        } catch (\Exception $e) {
            // 查询异常
            throw_strack_exception('request error!', ErrorCode::DB_SELECT_ERROR);
        }
    }
}
